
package org.madason.humanresources.datalayer;

import java.util.*;
import java.sql.*;
import java.util.Date;

/**
 * 
 */
public class JobAccessOracle extends JobAccess
{
	protected java.sql.Timestamp convertDate(java.util.Date	date)
	{
		if (date == null) return null;
		else return new java.sql.Timestamp(date.getTime());
	}

	protected java.util.Date convertDate(java.sql.Timestamp	date)
	{
		if (date == null) return null;
		else return new java.util.Date(date.getTime());
	}
		
	
    /**
     * insert new Job
     */    
	public void insert(Connection conn, Job bean) throws Exception
	{
		// check whether bean is an subclass class
		
		insertIntern(conn, bean);
	}


    /**
     * internal implementation of insert
     */
	public void insertIntern(Connection conn, Job bean) throws Exception
	{

		String sqlCmd =
			"INSERT INTO \"JOBS\" (\"JOB_ID\", \"JOB_TITLE\", \"MIN_SALARY\", \"MAX_SALARY\") VALUES ( ?, ?, ?, ? )";

		PreparedStatement stm = null;

		try
		{
			
			stm = conn.prepareStatement(sqlCmd);
        
        

			if (bean.id == null)
				stm.setNull(1, Types.VARCHAR);
			else
				stm.setString(1, bean.getId());
			if (bean.title == null)
				stm.setNull(2, Types.VARCHAR);
			else
				stm.setString(2, bean.getTitle());
			stm.setInt(3, bean.getMinSalary());
			stm.setInt(4, bean.getMaxSalary());
			
			stm.executeUpdate();
		}
		finally
		{
			if (stm != null) stm.close();
		}
	}
	

	public void delete(Connection conn, String primaryKey) throws Exception
	{			
		PreparedStatement stm = null;

		try
		{
			String sqlCmd =
				"DELETE FROM \"JOBS\" WHERE \"JOB_ID\" = ?";

            stm = conn.prepareStatement(sqlCmd);

			stm.setString(1, primaryKey);
			stm.executeUpdate();
		}
		finally
		{
			if (stm != null) stm.close();
		}
		
	}


	public void update(Connection conn, Job bean) throws Exception
	{
		// check whether bean is an subclass class
		
		updateIntern(conn, bean);
	}


	public void updateIntern(Connection conn, Job bean) throws Exception
	{				

		PreparedStatement stm = null;

		try
		{                    
			
			String sqlCmd =
				"UPDATE \"JOBS\" "+
				"SET \"JOB_TITLE\" = ?, \"MIN_SALARY\" = ?, \"MAX_SALARY\" = ? "+
				"WHERE \"JOB_ID\" = ? " +                    
				"";
			stm = conn.prepareStatement(sqlCmd);

			if (bean.getTitle() == null)				
				stm.setString(1, bean.getTitle());			
			else
				stm.setString(1, bean.getTitle());			
			stm.setInt(2, bean.getMinSalary());			
			stm.setInt(3, bean.getMaxSalary());				
				


			stm.setString(4, bean.getId());						
                    	                    
			int count = stm.executeUpdate();
			if (count == 0) throw new Exception("Der Datensatz wurde zwischzeitlich bearbeitet oder geloescht");
		}
		finally
		{
			if (stm != null) stm.close();
		}
	}
		
	

		
				
		
	
	public List<Job> findAll(Connection conn) throws Exception
	{
		List<Job> results = new ArrayList<Job>();

		PreparedStatement stm = null;
		ResultSet rs = null;
		
		try
		{
			String sqlCmd = "SELECT \"JOBS\".\"JOB_ID\", \"JOBS\".\"JOB_TITLE\", \"JOBS\".\"MIN_SALARY\", \"JOBS\".\"MAX_SALARY\" FROM \"JOBS\"";

			stm = conn.prepareStatement(sqlCmd);

			rs = stm.executeQuery();
											
			int idx_Job_id = rs.findColumn("JOB_ID");
			int idx_Job_title = rs.findColumn("JOB_TITLE");
			int idx_Job_minSalary = rs.findColumn("MIN_SALARY");
			int idx_Job_maxSalary = rs.findColumn("MAX_SALARY");																							
						
						
			while (rs.next())
			{
				// bean anlegen							
				Job bean = Job.getInstance();		

																								
	
				bean.setId(rs.getString(idx_Job_id));
				bean.setTitle(rs.getString(idx_Job_title));
				bean.setMinSalary(rs.getInt(idx_Job_minSalary));
				bean.setMaxSalary(rs.getInt(idx_Job_maxSalary));
																																		
				
				results.add(bean);
			}
		}
		finally
		{
			if (rs != null) rs.close();
			if (stm != null) stm.close();
		}			
		
		return results;
	}		
	
	
	public List<Job> findByName(Connection conn, String title) throws Exception
	{
		List<Job> results = new ArrayList<Job>();

		PreparedStatement stm = null;
		ResultSet rs = null;
		
		try
		{
			String sqlCmd = "SELECT JOBS.JOB_ID, JOBS.JOB_TITLE, JOBS.MIN_SALARY, JOBS.MAX_SALARY FROM JOBS WHERE JOB_TITLE like ?";

			stm = conn.prepareStatement(sqlCmd);

			stm.setString(1, title);
			rs = stm.executeQuery();
											
			int idx_Job_id = rs.findColumn("JOB_ID");
			int idx_Job_title = rs.findColumn("JOB_TITLE");
			int idx_Job_minSalary = rs.findColumn("MIN_SALARY");
			int idx_Job_maxSalary = rs.findColumn("MAX_SALARY");																							
						
						
			while (rs.next())
			{
				// bean anlegen							
				Job bean = Job.getInstance();		

																								
	
				bean.setId(rs.getString(idx_Job_id));
				bean.setTitle(rs.getString(idx_Job_title));
				bean.setMinSalary(rs.getInt(idx_Job_minSalary));
				bean.setMaxSalary(rs.getInt(idx_Job_maxSalary));
																																		
				
				results.add(bean);
			}
		}
		finally
		{
			if (rs != null) rs.close();
			if (stm != null) stm.close();
		}			
		
		return results;
	}		
	
	
	public Job findByPrimaryKey(Connection conn, String id) throws Exception
	{

		PreparedStatement stm = null;
		ResultSet rs = null;
		
		try
		{
			String sqlCmd = "SELECT \"JOBS\".\"JOB_ID\", \"JOBS\".\"JOB_TITLE\", \"JOBS\".\"MIN_SALARY\", \"JOBS\".\"MAX_SALARY\" FROM \"JOBS\" WHERE \"JOBS\".\"JOB_ID\" = ?";

			stm = conn.prepareStatement(sqlCmd);

			stm.setString(1, id);
			rs = stm.executeQuery();
											
			int idx_Job_id = rs.findColumn("JOB_ID");
			int idx_Job_title = rs.findColumn("JOB_TITLE");
			int idx_Job_minSalary = rs.findColumn("MIN_SALARY");
			int idx_Job_maxSalary = rs.findColumn("MAX_SALARY");																							
						
						
			while (rs.next())
			{
				// bean anlegen							
				Job bean = Job.getInstance();		

																								
	
				bean.setId(rs.getString(idx_Job_id));
				bean.setTitle(rs.getString(idx_Job_title));
				bean.setMinSalary(rs.getInt(idx_Job_minSalary));
				bean.setMaxSalary(rs.getInt(idx_Job_maxSalary));
																																		
				
				return bean;
			}
		}
		finally
		{
			if (rs != null) rs.close();
			if (stm != null) stm.close();
		}			
		
		return null;
	}		
	
	
}
  

	





